use anyhow::Result;

use sqlx::{types, MySql, Pool};
use std::str::FromStr;
use std::collections::HashMap;

const INPUT: &str = "06ff7f83-6544-482c-a133-124a384594ba | url        | node3-86      |             0 |        1 |         0 |          1 | 2021-09-30 17:56:59
07eca396-3c32-452f-a2fd-faa6146a1e45 | url        | node2-105     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:00
108d3050-1fd3-42ff-965e-8a6babf19110 | url        | node2-113     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:59
178a2fa4-51b7-4859-9326-5ff46692512e | url        | node2-6       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
18163a78-ad6e-4c7d-8968-79dfc4ae6916 | url        | node3-3       |             1 |        1 |         0 |          1 | 2021-09-30 18:37:57
1926a234-9db5-4b83-9780-afdd76240eb6 | url        | node3-84      |             0 |        1 |         0 |          1 | 2021-09-30 17:56:59
19a245cf-f28b-49d5-ba1a-783e8acb6164 | url        | node2-110     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:57
1aac6043-6931-4e8c-8494-806e4909d541 | url        | node3-71      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:52
1fa810b6-05d6-4f82-b06d-31cdaafa67c6 | url        | node3-72      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:52
200ebe74-13ed-40e4-b18d-a06483321886 | url        | node2-16      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:05
21497f41-8912-44e8-93d6-792c5936ca57 | url        | node2-108     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:01
21cf6c1a-219f-46e4-861c-60629ac0f660 | url        | node2-15      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
2ee6773d-778f-400f-ba16-d3e17f39516b | url        | node2-100     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:00
323cffb6-e3a4-4cca-bf4c-cfa061d38624 | url        | node3-74      |             0 |        1 |         0 |          1 | 2021-09-30 17:56:59
38773a40-b6e2-44b2-bf35-67b69782c439 | url        | node2-13      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
39d10319-6011-4911-8aa8-526fdb5e4ba2 | url        | node1-6       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
3bd31c23-6e1c-4d47-a37c-2cc7b8017392 | url        | node3-127     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:54
3f594fc0-0317-4b10-bb29-fad18b2ab0f6 | url        | node1-44      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
3f6e3cdf-8974-429a-b2e2-92e121270f5f | url        | node2-102     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:01
46b49506-7d95-40a0-ab58-20cd789bf8e7 | url        | node1-39      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
5972cf2e-ce1f-4d0d-bbc6-be27fedaedaa | url        | node3-14      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:54
5c336249-9b14-4eb8-be3b-84c20ee64100 | url        | node3-126     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:52
60e18075-7aad-426a-83fb-7704d18b439a | url        | node3-66      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:48
68fd1825-14c9-4611-9fe2-5493102e2681 | url        | node2-2       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:00
6c1eb101-dfad-4fff-85c6-136c6578b714 | url        | node3-77      |             0 |        1 |         0 |          1 | 2021-09-30 17:57:28
6c9a9125-3a2b-4bb3-ba9f-5e3bbec395f3 | url        | node3-69      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:52
728c0167-db44-4d1b-867b-6c73f9fc6ed1 | url        | node1-27      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
734f621f-5bf6-464a-ab74-5341ba225dbd | url        | node3-76      |             0 |        1 |         0 |          1 | 2021-09-30 17:57:05
75f6667e-c36c-47cd-b993-1b501075e133 | url        | node3-6       |             1 |        1 |         0 |          1 | 2021-09-30 18:37:56
77ec2b7c-da25-4b4e-939f-1c667ab4ea27 | url        | node3-9       |             1 |        1 |         0 |          1 | 2021-09-30 18:37:56
784184ea-df99-4256-b00b-22e0e61b2cdf | url        | node2-101     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:01
9010bf93-ef00-4d9c-b80d-c9783c667414 | url        | node2-111     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:58
92094281-34f4-4129-9f09-a3e5d78f89f0 | url        | node2-116     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:00
92a09ff0-d694-4d3a-bb3d-9719b5aca18f | url        | node3-10      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:56
97701d24-d147-41a8-a5e5-6e103a0627ae | url        | node3-70      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:52
98be9291-d3f5-4031-ae07-0d193b32c90c | url        | node1-32      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:09
a1c05eab-429d-4e92-b549-44e11aabebb2 | url        | node2-112     |             1 |        1 |         0 |          1 | 2021-09-30 18:37:58
a343ec4a-8e36-497c-a1ef-12f7c71bf9ec | url        | node2-7       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
a75e4fed-69fc-4535-b607-e0e4e6bd46fd | url        | node3-78      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:49
a7a6341d-2370-4869-bb50-3ccd05b527e0 | url        | node2-3       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:00
a9352b2b-34da-4bf3-8f2a-83047cc5ea63 | url        | miner-node5-2 |             1 |        0 |         1 |          1 | 2021-09-30 18:35:58
aa612e16-66ad-4d0b-b334-1a5bff845d41 | url        | node3-2       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
abdd5eb8-df97-4412-92fa-822999501975 | url        | node1-22      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
b2309dd5-64fc-401d-9cb2-098b8a6835db | url        | node2-107     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:01
ba151402-6390-4253-8266-51de7afa23f2 | url        | node3-68      |             0 |        1 |         0 |          1 | 2021-09-30 17:57:09
c050d277-ba23-4d99-a351-dc416acb94f4 | url        | node2-5       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
c61330d8-95db-48b8-bc11-a902a3c3e34d | url        | node2-18      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:05
c88b65f5-e2c6-45ab-b74d-7b4fb36a15df | url        | node1-28      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
c9005a5f-53e9-43ee-8173-436b35d5f023 | url        | node2-14      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
ce8cf6bb-be63-4d8f-94e1-0b159f0164b3 | url        | node1-26      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
cfdaf72f-f12a-4a81-a231-a0446b7647d8 | url        | node2-4       |             1 |        1 |         0 |          1 | 2021-09-30 18:38:04
d2e8582c-c8bc-4468-ba53-f7e1f32efb5c | url        | node3-11      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:53
d62851a1-260b-4081-9e3c-35aa76d492e5 | url        | node3-16      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:56
da0f7324-77c3-412f-8b72-fc1f8bfc985c | url        | node2-106     |             1 |        1 |         0 |          1 | 2021-09-30 18:38:01
e05682a4-2af1-4028-a5f6-b8a65daba0cf | url        | node2-17      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:05
e21e6fb2-bd3f-4404-bbbc-4af6b8c5cfa9 | url        | node3-75      |             0 |        1 |         0 |          1 | 2021-09-30 17:57:06
e4a6e902-4fa0-43cf-a7dc-21190855271b | url        | node3-82      |             0 |        1 |         0 |          1 | 2021-09-30 17:56:59
e7bffae8-bb64-4b2a-9e26-e566d32edd58 | url        | node1-43      |             1 |        1 |         0 |          1 | 2021-09-30 18:38:08
f1030a55-f895-4342-9d6c-064591289155 | url        | node3-12      |             1 |        1 |         0 |          1 | 2021-09-30 18:37:54
f4f01cce-bc6d-4902-9cb7-404b705a3b0c | url        | node3-85      |             0 |        1 |         0 |          1 | 2021-09-30 17:57:00";

pub async fn parse_worker_online(pool: &Pool<MySql>) -> Result<()> {
    for line in INPUT.lines() {
        let vec: Vec<&str> = line.split("|").collect();
        let vec: Vec<&str> = vec.iter().map(|x| x.trim()).collect();
        let insert = "insert into worker (worker_id, worker_url, worker_host, worker_enable, can_seal, can_store, accept_new, update_time) values (?, ?, ?, ?, ?, ?, ?, ?)";
        let ret = sqlx::query(insert)
            .bind(vec[0])
            .bind(vec[1])
            .bind(vec[2])
            .bind(vec[3])
            .bind(vec[4])
            .bind(vec[5])
            .bind(vec[6])
            .bind(vec[7])
            .execute(pool)
            .await?;

        log::info!("rows affect {}", ret.rows_affected());
    }

    Ok(())
}

#[derive(sqlx::FromRow, Debug)]
pub struct TaskOld {
    pub id: String,
    pub addpiecestart: Option<String>,
    pub addpieceend: Option<String>,
    pub addpiecehost: Option<String>,
    pub p1start: Option<String>,
    pub p1end: Option<String>,
    pub p1host: Option<String>,
    pub p2start: Option<String>,
    pub p2end: Option<String>,
    pub p2host: Option<String>,
    pub c1start: Option<String>,
    pub c1end: Option<String>,
    pub c1host: Option<String>,
    pub c2start: Option<String>,
    pub c2end: Option<String>,
    pub c2host: Option<String>,
}

pub async fn transfer_old_from_sqlite(pool: &Pool<MySql>) -> Result<()> {
    let mut map: HashMap<&str, String> = HashMap::new();
    for line in INPUT.lines() {
        let vec: Vec<&str> = line.split("|").collect();
        let vec: Vec<&str> = vec.iter().map(|x| x.trim()).collect();
        map.insert(vec[2], vec[0].to_owned());
    }
    let query = "select * from sector_sqlite";
    let itask = "insert into seal_task (task_id, task_type, sector_number, miner_actor, worker_id, assign_time, receive_time, complete_time, task_status)
values (uuid(), ?, ?, ?, ?, sysdate(), ?, ?, 1)";
    let isector = "insert into sector (sector_number, miner_actor, sector_stage, worker_id, update_time, sector_status) values (?, ?, ?, ?, sysdate(), 1)";
    let vec = sqlx::query_as::<_, TaskOld>(query).fetch_all(pool).await?;
    for to in vec {
        let id = to.id;
        let list: Vec<&str> = id.split("-").collect();
        let number = list[2];
        let miner = 1069894i64;
        let number = i64::from_str(number).unwrap();

        let mut worker = None;
        let mut stage = None;

        // log::info!("rows affect {}", ret.rows_affected());

        if let Some(host) = to.addpiecehost {
            let w = map.get(host.as_str());
            worker = w;
            stage = Some("AP");
            if let Some(w) = w {
                let _ = sqlx::query(itask)
                    .bind("AP")
                    .bind(number)
                    .bind(miner)
                    .bind(w)
                    .bind(handle_time(to.addpiecestart))
                    .bind(handle_time(to.addpieceend))
                    .execute(pool)
                    .await?;
            }
        }
        if let Some(host) = to.p1host {
            let w = map.get(host.as_str());
            worker = w;
            stage = Some("PC1");
            if let Some(w) = w {
                let _ = sqlx::query(itask)
                    .bind("PC1")
                    .bind(number)
                    .bind(miner)
                    .bind(w)
                    .bind(handle_time(to.p1start))
                    .bind(handle_time(to.p1end))
                    .execute(pool)
                    .await?;
            }

        }

        if let Some(host) = to.p2host {
            let w = map.get(host.as_str());
            worker = w;
            stage = Some("PC2");
            if let Some(w) = w {
                let _ = sqlx::query(itask)
                    .bind("PC2")
                    .bind(number)
                    .bind(miner)
                    .bind(w)
                    .bind(handle_time(to.p2start))
                    .bind(handle_time(to.p2end))
                    .execute(pool)
                    .await?;
            }

        }

        if let Some(host) = to.c1host {
            let w = map.get(host.as_str());
            worker = w;
            if let Some(w) = w {
                stage = Some("C1");
                let _ = sqlx::query(itask)
                    .bind("C1")
                    .bind(number)
                    .bind(miner)
                    .bind(w)
                    .bind(handle_time(to.c1start))
                    .bind(handle_time(to.c1end))
                    .execute(pool)
                    .await?;
            }
        }

        if let Some(host) = to.c2host {
            let w = map.get(host.as_str());
            worker = w;
            stage = Some("C2");
            if let Some(w) = w {
                let _ = sqlx::query(itask)
                    .bind("C2")
                    .bind(number)
                    .bind(miner)
                    .bind(w)
                    .bind(handle_time(to.c2start))
                    .bind(handle_time(to.c2end))
                    .execute(pool)
                    .await?;
            }
        }
        if let Some(w) = worker {
            let _ = sqlx::query(isector)
                .bind(number)
                .bind(miner)
                .bind(stage)
                .bind(worker)
                .execute(pool)
                .await?;
        }
    }

    Ok(())
}

pub fn handle_time(input: Option<String>) -> Option<String> {
    if let Some(input) = input {
        let vec: Vec<&str> = input.split("+").collect();
        let time = vec[0];
        let time = time.replace("T", " ");
        Some(time)
    } else {
        None
    }
}

#[derive(sqlx::FromRow, Debug)]
pub struct WorkerIds {
    pub old_id: String,
    pub new_id: String,
}

pub async fn update_worker_id(pool: &Pool<MySql>) -> Result<()> {
    let query_id = "select w.worker_id as new_id, wi.worker_id as old_id from worker w, seal_workers_info wi where w.worker_host = wi.worker_host";
    let vec = sqlx::query_as::<_, WorkerIds>(query_id).fetch_all(pool).await?;
    let update_sector = "update sector set worker_id = ? where worker_id = ?";
    let update_task = "update seal_task set worker_id = ? where worker_id = ?";
    for w in vec {
        let _ = sqlx::query(update_sector)
            .bind(&w.new_id)
            .bind(&w.old_id)
            .execute(pool)
            .await?;
        let _ = sqlx::query(update_task)
            .bind(&w.new_id)
            .bind(&w.old_id)
            .execute(pool)
            .await?;
    }
    Ok(())
}